# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock.py
#日期：2018-10-31
#备注：获取东方财富网的股票交易tradeInfo_to_mysql()和股票基本信息stock_to_mysql() 
   
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
import math
import time
import threading
from datetime import datetime
from pycacho.cachobase.chinese_workday import get_trade_day
from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.logger import Logger

logger = Logger("get_stock").get_log()
lock = threading.Lock()
td_date =get_trade_day(0,'s')     # %Y%m%d '20220623'
table = 'stock_tradeInfo_' + td_date

def createTable():
    sql1 = 'drop table if exists %s ,china_stock;'%(table)  # ,
    sql2 = 'create table %s (trade_date VARCHAR(20),stock_no VARCHAR(10),' \
           'stock_name VARCHAR(10),price_new float,price_chg float,price_max float,price_min float,price_change float,' \
           'price_amplitude float,price_today float,price_yesterday float,volume float,turnover float,turnover_rate float,' \
           'trailing_PE float,PB float,total_value float,market_value float,market_now VARCHAR(30),PRIMARY KEY (stock_no));'%(table)
    sql3 = 'create table china_stock(stock_type VARCHAR(10),stock_no VARCHAR(10),stock_name VARCHAR(10),PRIMARY KEY (stock_no));'

    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    db.commit()


# 将股票信息（股票类型（1-沪市，2-深市），股票代码，股票简称）
def stock_to_mysql(stock_type, stock_no, stock_name):
    sql = "insert into china_stock (stock_type,stock_no,stock_name) values ('%s','%s','%s');" \
          % (stock_type, stock_no, stock_name)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()


# 将股票交易信息（股票每天交易价、交易量、涨跌、市值）
def tradeInfo_to_mysql(table,trade_date,stock_no, stock_name, price_new, price_chg, price_max, price_min, price_change, price_amplitude,
                       price_today, price_yesterday, volume, turnover, turnover_rate, trailing_PE, PB, total_value,
                       market_value, market_now):
    sql = "insert into %s (trade_date,stock_no,stock_name,price_new,price_chg,price_max,price_min," \
          "price_change,price_amplitude,price_today,price_yesterday,volume,turnover,turnover_rate,trailing_PE,PB," \
          "total_value,market_value,market_now) values" \
          "('%s','%s','%s','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%s');" \
          % (table,trade_date,stock_no, stock_name, price_new, price_chg, price_max, price_min, price_change, price_amplitude,
             price_today, price_yesterday, volume, turnover, turnover_rate, trailing_PE, PB, total_value, market_value,
             market_now)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        logger.info('%s股票交易信息插入成功！',stock_no)


# 获取股票交易信息
def get_stock_old(p):
    url = 'http://nufm.dfcfw.com/EM_Finance2014NumericApplication/JS.aspx?type=CT&token=4f1862fc3b5e77c150a2b985b12db0fd&sty=FCOIATC&cmd=C._A&st=(ChangePercent)' \
          '&p={0}&ps=20'.format(p)
    resp = requests.get(url, headers=headers)
    print(url)
    data = resp.text
    data1 = data.replace('([', '').replace('])', '')
    data2 = data1.replace('\",\"', ']').replace('\"', '')
    data3 = data2.split(']')
    #print("爬取的链接为：", url)
    for stock in data3:
        info = stock.split(',')
        print(info)
        stock_type = info[0]  # 股票类型（1-沪市，2-深市）
        stock_no = info[1]  # 股票代码
        stock_name = info[2]  # 股票简称
        if info[3] != '-':
            price_new = float(info[3])  # 股票最新价
        else:
            price_new = 0
        if info[5] != '-':
            price_chg = float(info[5])  # 股票涨跌幅 %
        else:
            price_chg = 0
        if info[9] != '-':
            price_max = float(info[9])  # 股票最高价
        else:
            price_max = 0
        if info[10] != '-':
            price_min = float(info[10])  # 股票最低价
        else:
            price_min = 0
        if info[4] != '-':
            price_change = float(info[4])  # 股票涨跌额
        else:
            price_change = 0
        if info[8] != '-':
            price_amplitude = float(info[8])  # 股票振幅 %
        else:
            price_amplitude = 0
        if info[11] != '-':
            price_today = float(info[11])  # 股票开盘价
        else:
            price_today = 0
        if info[12] != '-':
            price_yesterday = float(info[12])  # 股票昨天收盘价
        else:
            price_yesterday = 0
        if info[6] != '-':
            volume = float(info[6])  # 股票成交量 单位：手
        else:
            volume = 0
        if info[7] != '-':
            turnover = float(info[7])  # 股票成交额
        else:
            turnover = 0
        if info[15] != '-':
            turnover_rate = float(info[15])  # 股票换手率 %
        else:
            turnover_rate = 0
        if info[16] != '-':
            trailing_PE = float(info[16])  # 股票动态市盈率
        else:
            trailing_PE = 0
        if info[17] != '-':
            PB = float(info[17])  # 市净率
        else:
            PB = 0
        if info[18] != '-':
            total_value = float(info[18])  # 股票总市值(元)
        else:
            total_value = 0
        if info[19] != '-':
            market_value = float(info[19])  # 股票流通值(元)
        else:
            market_value = 0
        market_now = info[24]  # 股票现价时间

        with lock:  # 线程锁lock请求
            # 将股票信息插入到主表china_stock
            stock_to_mysql(stock_type, stock_no, stock_name)
            # 将信息插入到stock_tradeInfo
            tradeInfo_to_mysql(table,td_date,stock_no, stock_name, price_new, price_chg, price_max, price_min, price_change,
                           price_amplitude, price_today, price_yesterday, volume, turnover, turnover_rate, trailing_PE,
                           PB,total_value, market_value, market_now)

# 获取总股票数量
def get_stock_num(num):
    url = 'http://7.push2.eastmoney.com/api/qt/clist/get?&pn=1&pz={0}&fltt=2&' \
          'fs=m:0+t:6,m:0+t:13,m:0+t:80,m:1+t:2,m:1+t:23&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152'.format(num)
    resp = requests.get(url, headers=headers)
    data = resp.json()['data']
    total_num = data['total']
    total_page = math.ceil(total_num / num)
    print(type(total_page))
    return total_page


# 获取股票交易信息
def get_stock(p,num):
    url = 'http://7.push2.eastmoney.com/api/qt/clist/get?&pn={0}&pz={1}&fltt=2&' \
          'fs=m:0+t:6,m:0+t:13,m:0+t:80,m:1+t:2,m:1+t:23&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152'.format(p,num)
    resp = requests.get(url, headers=headers)
    data = resp.json()['data']
    for i in range(num):
        stock_info = data['diff'][str(i)]
        #print(stock_info)
        stock_type = stock_info['f13']  # 股票类型（1-沪市，0-深市）
        stock_no = stock_info['f12']   # 股票代码
        #print(stock_type,stock_no)
        stock_name = stock_info['f14'] # 股票简称
        price_new = stock_info['f2']   # 股票最新价
        price_chg = stock_info['f3']   # 股票涨跌幅 %
        price_max = stock_info['f15']  # 股票最高价
        price_min = stock_info['f16']  # 股票最低价
        price_change = stock_info['f4']  # 股票涨跌额
        price_amplitude = stock_info['f7']  # 股票振幅 %
        price_today = stock_info['f17']  # 股票开盘价
        price_yesterday = stock_info['f18']  # 股票昨天收盘价
        volume = stock_info['f5']           # 股票成交量 单位：手
        turnover = stock_info['f6']         # 股票成交额
        turnover_rate = stock_info['f8']  # 股票换手率 %
        trailing_PE = stock_info['f9']     # 股票动态市盈率
        PB = stock_info['f23']              # 市净率
        total_value = stock_info['f20']       # 股票总市值(元)
        market_value = stock_info['f21']      # 股票流通值(元)
        market_now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')      # 股票现价时间
        with lock:  # 线程锁lock请求
            # 将股票信息插入到主表china_stock
            stock_to_mysql(stock_type, stock_no, stock_name)
            # 将信息插入到stock_tradeInfo
            tradeInfo_to_mysql(table,td_date,stock_no, stock_name, price_new, price_chg, price_max, price_min, price_change,
                           price_amplitude, price_today, price_yesterday, volume, turnover, turnover_rate, trailing_PE,
                           PB,total_value, market_value, market_now)

def merge():
    sql1 = 'delete from stock_tradeinfo_total where trade_date=%s;' % (td_date)
    sql2 = 'insert into stock_tradeInfo_total(trade_date,stock_no,stock_name,price_new,price_chg,price_max,price_min,price_change,price_amplitude,price_today,price_yesterday,volume,turnover,turnover_rate,trailing_PE,PB,total_value,market_value,market_now) ' \
           'select trade_date,stock_no,stock_name,price_new,price_chg,price_max,price_min,price_change,price_amplitude,price_today,price_yesterday,volume,turnover,turnover_rate,trailing_PE,PB,total_value,market_value,market_now from %s where price_new>0;'%(table)
    sql3 = 'delete from daily_report where report_date=%s;' % (td_date)
    sql4 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "10up_3",count(*),"创业板涨幅10以上股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)="300" and price_chg>9.8;'%(td_date,td_date)
    sql5 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "10down_3",count(*),"创业板跌幅-10股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)="300" and price_chg<-9.8;'%(td_date,td_date)
    sql6 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "05up_3",count(*),"创业板涨幅5以上股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)="300" and price_chg>5;'%(td_date,td_date)
    sql7 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "05down_3",count(*),"创业板跌幅-5股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)="300" and price_chg<-5;'%(td_date,td_date)
    sql8 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "20up",count(*),"涨幅20股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)<>"688" and price_chg>19.8;'%(td_date,td_date)
    sql9 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "20down",count(*),"跌幅-20股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)<>"688" and price_chg<-19.8;' % (td_date, td_date)
    sql10 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "10up",count(*),"非创业板涨幅10以上股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)<>"300" and price_chg>9.8;'%(td_date,td_date)
    sql11 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "10down",count(*),"非创业板跌幅-10股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)<>"300" and price_chg<-9.8;'%(td_date,td_date)
    sql12 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "05up",count(*),"非创业板涨幅5以上股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)<>"300" and price_chg>5;'%(td_date,td_date)
    sql13 = 'insert into daily_report(report_date,report_type,report_num,report_remark,create_time) select "%s", "05down",count(*),"非创业板跌幅-5股票数量",now() from stock_tradeinfo_total ' \
           'where trade_date="%s" and SUBSTR(stock_no,1,3)<>"300" and price_chg<-5;'%(td_date,td_date)
    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    cursor.execute(sql4)
    cursor.execute(sql5)
    cursor.execute(sql6)
    cursor.execute(sql7)
    cursor.execute(sql8)
    cursor.execute(sql9)
    cursor.execute(sql10)
    cursor.execute(sql11)
    cursor.execute(sql12)
    cursor.execute(sql13)

    db.commit()
    logger.info("每天交易数据合并到总表stock_tradeinfo_total成功！")


def main():
    createTable()
    total_page = int(get_stock_num(100))
    threads = [threading.Thread(target=get_stock, args=(t,100)) for t in range(total_page)]     # 第261页
    for t in threads:
        t.start()

    for t in threads:
        t.join()


if __name__ == '__main__':
    cTime = time.time()
    main()
    logger.info("总共耗时：%.2f s ", time.time() - cTime)
    merge()
    db.close()